# 转换Excel为Word
import pandas as pd
from docx import Document
from src.utils import is_digits_and_uppercase
from src.utils import traversal_folder
from src.utils import number_to_english

# order_no_pre = "HDH2508WF"
order_no_pre = "JY25066"
# 报关资料所在文件夹（需要修改）
folder_path = "/Users/wangzhiwei/Desktop/test"
# 报关资料序号前缀路径（需要修改）
prePath = "test/敬业 散货"
# 大副收据保存文件夹（需要修改）
word_path = "/Users/wangzhiwei/Desktop/大副收据"

def modify_word(template_file_path, data, fileName):
    doc = Document(template_file_path)

    # 替换段落内容
    for para in doc.paragraphs:
        find_and_replace(para.runs, data)
    # 替换表格内容
    for table in doc.tables:
        for row in table.rows:
            for cell in row.cells:
                for para in cell.paragraphs:
                    find_and_replace(para.runs, data)
    doc.save(word_path + "/" + fileName)
# 查找和替换
def find_and_replace(runs, data):
    tempText = None
    startRunIndex = 0
    i = 0
    for run in runs:
        have = 0
        for key in data:
            old_text = key
            new_text = data[key]
            if old_text in run.text:
                run.text = run.text.replace(old_text, new_text)
                tempText = None
                startRunIndex = 0
                have = 1
                break
        if have == 0:
            if tempText != None:
                if "#" in run.text:
                    tempText = run.text
                    startRunIndex = i
                else:
                    tempText += run.text
                    haveInTempText = 0
                    for key in data:
                        old_text = key
                        new_text = data[key]
                        if old_text in tempText:
                            haveInTempText = 1
                            for j in range(startRunIndex,i + 1):
                                preRun = runs[j]
                                if j == i:
                                    preRun.text = tempText.replace(old_text, new_text)
                                else:
                                    preRun.text = ""
                            tempText = None
                            startRunIndex = 0
                            break
            elif "#" in run.text:
                tempText = run.text
                startRunIndex = i
        i += 1
# 读取单文件信息
def get_product_info(file_path):
    df = pd.read_excel(file_path, sheet_name='PL')
    product_info = {
        "#No": None,
        "#Shipper": None,
        "#Consignee": None,
        "#RealConsignee": None,
        "#Packages": None,
        "#GoodsName": None,
        "#GWeight": None,
        "#NWeight": None,
        "#Number": None,
    }
    USCI = df.iloc[3, 0]
    if USCI is not None and type(USCI) == str:
        product_info["#Shipper"] = df.iloc[0, 0] + "\n" + df.iloc[1, 0] + "\n" + USCI
    else:
        product_info["#Shipper"] = df.iloc[0, 0] + "\n" + df.iloc[1, 0]
    for col in range(len(df.columns)):
        for i in range(len(df)):
            value = df.iloc[i, col]
            if type(value) == str and "实际收货人" in value:
                product_info["#RealConsignee"] = ""
                single = False
                if "三方贸易，实际收货人如下，请按此订舱：" in value:
                    remainContent = value[len("三方贸易，实际收货人如下，请按此订舱："):]
                    if len(remainContent) >= 1:
                        single = True
                        product_info["#RealConsignee"] += value[len("三方贸易，实际收货人如下，请按此订舱："):]
                if not single:
                    real_consignee_buffer = ""
                    step = 1
                    isEnd = False
                    real_consignee = df.iloc[i + step, col]
                    while ((real_consignee is not None and type(real_consignee) is str) or not isEnd) :
                        if real_consignee is not None and type(real_consignee) is str:
                            if real_consignee_buffer == "":
                                real_consignee_buffer += real_consignee
                            else:
                                real_consignee_buffer += ("\n" + real_consignee)
                            isEnd = True
                        step+=1
                        real_consignee = df.iloc[i + step, col]
                    product_info["#RealConsignee"] += real_consignee_buffer
            if type(value) == str and "COMPANY" in value and col == 0:
                product_info["#Consignee"] = df.iloc[i, col + 1] + "\n" + df.iloc[i + 1, col + 1]
            if "TOTAL" == value:
                product_info["#Packages"] = f"{df.iloc[i, col + 2]}"
                product_info["#GWeight"] = f"{df.iloc[i, col + 4]}"
                product_info["#NWeight"] = f"{df.iloc[i, col + 5]}"
            if "NET" == value:
                product_name_list = None
                step = 1
                product_name = df.iloc[i + step, col + 1]
                product_name_map = {}
                while product_name is not None and type(product_name) is str :
                    if product_name_map.get(product_name) is None:
                        product_name_map.setdefault(product_name, 1)
                        if product_name_list is None:
                            product_name_list = product_name
                        else:
                            product_name_list += ("\n" + product_name)
                    step+=1
                    product_name = df.iloc[i + step, col + 1]
                product_info["#GoodsName"] = product_name_list
    return product_info
# 调用函数
files = traversal_folder(folder_path)
print("读取文件列表完成，开始打印...")
order_no_list = []
product_name_list = []
pks_list = []
gross_list = []
net_list = []
shipper_list = []
consignee_list = []
real_consignee_list = []
for file in files:
    print("数据抽取中：" + file)
    extNameIndex = file.find('.xlsx')
    if extNameIndex >= 0:
        keywordIndex = file.find(prePath)
        number = ''
        i = keywordIndex + len(prePath)
        numberChar = file[i]
        while is_digits_and_uppercase(numberChar):
            number += numberChar
            i += 1
            numberChar = file[i]

        product_info = get_product_info(file)
        product_info["#No"] = f"{order_no_pre}{number}"
        
        if product_info["#RealConsignee"] is not None:
            product_info["#Consignee"] = product_info["#RealConsignee"]
            del product_info["#RealConsignee"]
        product_info["#Number"] = number_to_english(product_info["#Packages"])
        modify_word("/Users/wangzhiwei/Desktop/template.docx", product_info, f"{order_no_pre}{number}-理货单.docx")
    else:
        print("非表格文件，跳过")

